<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ page language="java" import="java.sql.*"%>
<%
	Class.forName("org.postgresql.Driver");
	Connection conn = DriverManager.getConnection(
			"jdbc:postgresql://localhost:5432/cse132b", "cse132blogin",
			"1234567890");
	Statement stmt, stmt1, stmt2;

	if (request.getParameter("ACTION") != null) {
		if (request.getParameter("ACTION").equals("Insert")) {
			int NewID = 0;

			conn.setAutoCommit(false);

			PreparedStatement statement = conn.prepareStatement(
					"INSERT INTO thesis(name) VALUES (?)",
					Statement.RETURN_GENERATED_KEYS);
			statement.setString(1,
					request.getParameter("txtThesisName"));
			statement.executeUpdate();
			ResultSet rs = statement.getGeneratedKeys();
			if (rs.next()) {
				NewID = rs.getInt(1);
			}
			rs.close();
			statement.close();

			String arrStudentName[] = request
					.getParameterValues("sltStudentName");
			for (int i = 0; i < arrStudentName.length; i++) {
				PreparedStatement statement3 = conn
						.prepareStatement("INSERT INTO works_on(student_id, thesis_id) VALUES (?,?)");
				statement3.setInt(1,
						Integer.parseInt(arrStudentName[i]));
				statement3.setInt(2, NewID);
				statement3.executeUpdate();
				statement3.close();
			}

			String arrFaculty[] = request
					.getParameterValues("sltFaculty");
			for (int i = 0; i < arrFaculty.length; i++) {
				PreparedStatement statement3 = conn
						.prepareStatement("INSERT INTO committee(faculty_id, thesis_id) VALUES (?,?)");
				statement3.setInt(1, Integer.parseInt(arrFaculty[i]));
				statement3.setInt(2, NewID);
				statement3.executeUpdate();
				statement3.close();
			}

			conn.commit();
			conn.setAutoCommit(true);
		}
		
		if (request.getParameter("ACTION").equals("Update")){
			
			int EditID = Integer.valueOf(request.getParameter("PARAMETER"));
			conn.setAutoCommit(false);

			PreparedStatement statement = conn.prepareStatement(
					"UPDATE thesis SET name = ? WHERE thesis_id = ?");
			statement.setString(1, request.getParameter("txtThesisName"+ String.valueOf(EditID)));
			statement.setInt(2, EditID);
			
			statement.executeUpdate();
			statement.close();

					
			statement = conn
					.prepareStatement("DELETE FROM works_on WHERE thesis_id = ?");
			statement.setInt(1, EditID);
			statement.executeUpdate();
			statement.close();
			
			String arrStudentName[] = request
					.getParameterValues("sltStudentName"+ String.valueOf(EditID));
			for (int i = 0; i < arrStudentName.length; i++) {
				statement = conn
						.prepareStatement("INSERT INTO works_on(student_id, thesis_id) VALUES (?,?)");
				statement.setInt(1,
						Integer.parseInt(arrStudentName[i]));
				statement.setInt(2, EditID);
				statement.executeUpdate();
				statement.close();
			}

					
			statement = conn
					.prepareStatement("DELETE FROM committee WHERE thesis_id = ?");
			statement.setInt(1, EditID);
			statement.executeUpdate();
			statement.close();		
			
			String arrFaculty[] = request
					.getParameterValues("sltFaculty"+ String.valueOf(EditID));
			for (int i = 0; i < arrFaculty.length; i++) {
				PreparedStatement statement3 = conn
						.prepareStatement("INSERT INTO committee(faculty_id, thesis_id) VALUES (?,?)");
				statement3.setInt(1, Integer.parseInt(arrFaculty[i]));
				statement3.setInt(2, EditID);
				statement3.executeUpdate();
				statement3.close();
			}

			conn.commit();
			conn.setAutoCommit(true);
		}
		
		if (request.getParameter("ACTION").equals("Delete")){
			
			int EditID = Integer.valueOf(request.getParameter("PARAMETER"));
			conn.setAutoCommit(false);
					
			PreparedStatement statement = conn
					.prepareStatement("DELETE FROM works_on WHERE thesis_id = ?");
			statement.setInt(1, EditID);
			statement.executeUpdate();
			statement.close();
			
			
			statement = conn
					.prepareStatement("DELETE FROM committee WHERE thesis_id = ?");
			statement.setInt(1, EditID);
			statement.executeUpdate();
			statement.close();
			
			
			statement = conn.prepareStatement(
					"DELETE FROM thesis WHERE thesis_id = ?");
			statement.setInt(1, EditID);
			
			statement.executeUpdate();
			statement.close();

			
			conn.commit();
			conn.setAutoCommit(true);
		}
	}
%>
<!DOCTYPE html>
<html>
<head>
<script type="text/javascript">
	function Insert() {
		document.getElementById("sltStudentName").required = true;
		document.getElementById("txtThesisName").required = true;
		document.getElementById("sltFaculty").required = true;
		document.getElementById("ACTION").value = "Insert";
	}
	function Update(row) {
		document.getElementById("sltStudentName"+row).required = true;
		document.getElementById("txtThesisName"+row).required = true;
		document.getElementById("sltFaculty"+row).required = true;
		document.getElementById("ACTION").value = "Update";
		document.getElementById("PARAMETER").value = row;		
	}
	function Delete(row) {
		document.getElementById("sltStudentName"+row).required = true;
		document.getElementById("txtThesisName"+row).required = true;
		document.getElementById("sltFaculty"+row).required = true;
		document.getElementById("ACTION").value = "Delete";
		document.getElementById("PARAMETER").value = row;		
	}
</script>
</head>
<body>
	<table border="1">
		<tr>
			<td valign="top" width="40%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>
				<form id="myForm" method="post" action="committee.jsp">
					<p align="center">
						<font size="6">Thesis Committee</font>
					</p>
					<table border="1">
						<tr>
							<th>Student Name</th>
							<th>Thesis Name</th>
							<th>Faculty Name</th>
							<th>Action</th>
						</tr>
						<%---------- Add ----------%>
						<tr>
							<td><select name="sltStudentName" id="sltStudentName"
								multiple>
									<%
										stmt = conn.createStatement();
										ResultSet rsStudent = stmt
												.executeQuery("SELECT * FROM students, phd WHERE students.student_id = phd.student_id");
										while (rsStudent.next()) {
									%>
									<option value="<%=rsStudent.getInt("student_id")%>"><%=rsStudent.getString("first_name") + " "
						+ rsStudent.getString("middle_name") + " "
						+ rsStudent.getString("last_name")%></option>
									<%
										}
										rsStudent.close();
										stmt.close();
									%>
							</select></td>
							<td><input id="txtThesisName" name="txtThesisName"
								type="text"></td>
							<td><select name="sltFaculty" id="sltFaculty" multiple>
									<%
										stmt = conn.createStatement();
										ResultSet rsFaculty = stmt.executeQuery("SELECT * FROM faculty");
										while (rsFaculty.next()) {
									%>
									<option value="<%=rsFaculty.getInt("Faculty_id")%>"><%=rsFaculty.getString("title") + ". "
						+ rsFaculty.getString("first_name") + " " + rsFaculty.getString("middle_name") + " " + rsFaculty.getString("last_name")%></option>
									<%
										}
										rsFaculty.close();
										stmt.close();
									%>
							</select></td>
							<td align="center"><input type="submit" value="Insert" onclick="Insert()"></td>
						</tr>
						<%---------- Edit ----------%>
						<%
							stmt = conn.createStatement();
							ResultSet rsThesis = stmt.executeQuery("SELECT * FROM Thesis");

							while (rsThesis.next()) {
						%>
						<tr>
							<td><select name="sltStudentName<%=rsThesis.getInt("thesis_id")%>" id="sltStudentName<%=rsThesis.getInt("thesis_id")%>"
								multiple>
									<%
										stmt1 = conn.createStatement();
											rsStudent = stmt1
													.executeQuery("SELECT * FROM students, phd WHERE students.student_id = phd.student_id");

											while (rsStudent.next()) {

												stmt2 = conn.createStatement();
												ResultSet rsStudentWorksOn = stmt2
														.executeQuery("SELECT * FROM works_on WHERE thesis_id = "
																+ String.valueOf(rsThesis
																		.getInt("thesis_id"))
																+ " AND student_id ="
																+ String.valueOf(rsStudent
																		.getInt("student_id")));

												if (rsStudentWorksOn.next()) {
									%>
									<option selected value="<%=rsStudent.getInt("student_id")%>"><%=rsStudent.getString("first_name") + " "
								+ rsStudent.getString("middle_name") + " "
								+ rsStudent.getString("last_name")%></option>
									<%
										} else {
									%>
									<option value="<%=rsStudent.getInt("student_id")%>"><%=rsStudent.getString("first_name") + " "
								+ rsStudent.getString("middle_name") + " "
								+ rsStudent.getString("last_name")%></option>
									<%
										}
												rsStudentWorksOn.close();
												stmt2.close();
											}
											rsStudent.close();
											stmt1.close();

									%>
							</select></td>
							<td><input type="text"
								id="txtThesisName<%=rsThesis.getInt("thesis_id")%>"
								name="txtThesisName<%=rsThesis.getInt("thesis_id")%>"
								value="<%=rsThesis.getString("name")%>"></td>
							<td><select name="sltFaculty<%=rsThesis.getInt("thesis_id")%>" id="sltFaculty<%=rsThesis.getInt("thesis_id")%>"
								multiple>
									<%
									
										stmt1 = conn.createStatement();
										rsFaculty = stmt1
													.executeQuery("SELECT * FROM faculty");

											while (rsFaculty.next()) {

												stmt2 = conn.createStatement();
												ResultSet rsCommittee = stmt2
														.executeQuery("SELECT * FROM Committee WHERE thesis_id = "
																+ String.valueOf(rsThesis
																		.getInt("thesis_id"))
																+ " AND faculty_id ="
																+ String.valueOf(rsFaculty
																		.getInt("Faculty_id")));

												if (rsCommittee.next()) {
									%>
									<option selected value="<%=rsFaculty.getInt("Faculty_id")%>"><%=rsFaculty.getString("title") + ". "
						+ rsFaculty.getString("first_name") + " " + rsFaculty.getString("middle_name") + " " + rsFaculty.getString("last_name")%></option>
									<%
										} else {
									%>
									<option value="<%=rsFaculty.getInt("Faculty_id")%>"><%=rsFaculty.getString("title") + ". "
						+ rsFaculty.getString("first_name") + " " + rsFaculty.getString("middle_name") + " " + rsFaculty.getString("last_name")%></option>
									<%
										}
												rsCommittee.close();
												stmt2.close();
											}
											rsStudent.close();
											stmt1.close();

									%>
							</select></td>
							<td><input type="submit" value="Update"
								onclick="Update(<%=rsThesis.getInt("thesis_id")%>)">
								
								<input type="submit" value="Delete"
								onclick="Delete(<%=rsThesis.getInt("thesis_id")%>)">
								
								
								</td>
						</tr>
						<%
							}
							rsThesis.close();
							stmt.close();
						%>
					</table>
					<input type="hidden" id="ACTION" name="ACTION" value="None">
					<input type="hidden" id="PARAMETER" name="PARAMETER" value="">
				</form>
			</td>
		</tr>
	</table>
</body>
</html>
<%
	// Close the Connection
	conn.close();
%>